﻿using System;
using System.Collections.Generic;
using System.Text;
using Maticsoft.DBUtility;
using System.Data.SqlClient;
using System.Data;
using ExtendModule;
using LabMS.BLL;
using LabMS.Model;


namespace ExtendBLL
{
    public class Recipients : LabMS.BLL.Recipients
    {
        public void ExtendAdd(LabMS.Model.Recipients model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Recipients(");
            strSql.Append("Numbers,Name,Type,MRecipients,EncodedTime,ReturnTime,BorrowedState,ReturnState,Comment,Operator,OperatDate)");
            strSql.Append(" values (");
            strSql.Append("@Numbers,@Name,@Type,@MRecipients,@EncodedTime,@ReturnTime,@BorrowedState,@ReturnState,@Comment,@Operator,@OperatDate)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Numbers", SqlDbType.VarChar,50),
					new SqlParameter("@Name", SqlDbType.VarChar,30),
					new SqlParameter("@Type", SqlDbType.VarChar,20),
					new SqlParameter("@MRecipients", SqlDbType.VarChar,30),
					new SqlParameter("@EncodedTime", SqlDbType.DateTime),
					new SqlParameter("@ReturnTime", SqlDbType.DateTime),
					new SqlParameter("@BorrowedState", SqlDbType.VarChar,20),
					new SqlParameter("@ReturnState", SqlDbType.VarChar,20),
					new SqlParameter("@Comment", SqlDbType.VarChar,100),
					new SqlParameter("@Operator", SqlDbType.VarChar,30),
					new SqlParameter("@OperatDate", SqlDbType.DateTime)};
            parameters[0].Value = model.Numbers;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Type;
            parameters[3].Value = model.MRecipients;
            parameters[4].Value = model.EncodedTime;
            parameters[5].Value = model.ReturnTime;
            parameters[6].Value = model.BorrowedState;
            parameters[7].Value = model.ReturnState;
            parameters[8].Value = model.Comment;
            parameters[9].Value = model.Operator;
            parameters[10].Value = model.OperatDate;

            CommandInfo comd1 = new CommandInfo();
            comd1.Parameters = parameters;
            comd1.CommandText = strSql.ToString();


            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update InstrumentAndConsumables set ");
            strSql2.Append("Quantity=@Quantity");
            strSql2.Append(" where Numbers=@Numbers ");
            SqlParameter[] parameters2 =
            {
                new SqlParameter("@Quantity", SqlDbType.Int,4),
                new SqlParameter("@Numbers", SqlDbType.VarChar,30)
            };
            parameters2[0].Value = 0;
            parameters2[1].Value = model.Numbers;

            CommandInfo comd2 = new CommandInfo();
            comd2.Parameters = parameters2;
            comd2.CommandText = strSql2.ToString();

            List<CommandInfo> listcomd = new List<CommandInfo>();
            listcomd.Add(comd1);
            listcomd.Add(comd2);

            try
            {
                DbHelperSQL.ExecuteSqlTran(listcomd);
            }
            catch
            {
                throw (new Exception("数据库执行错误!"));
            }
        }

        public void ExtendReturn(LabMS.Model.Recipients model)
        {
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("update Recipients set ");
            strSql1.Append("ReturnTime=@ReturnTime,");
            strSql1.Append("Comment=@Comment,");
            strSql1.Append("ReturnState=@ReturnState");
            strSql1.Append(" where Numbers=@Numbers ");
            SqlParameter[] parameters1 =
            {
                new SqlParameter("@ReturnTime", SqlDbType.DateTime,8),
                 new SqlParameter("@Comment", SqlDbType.VarChar,100),
                 new SqlParameter("@ReturnState", SqlDbType.VarChar,20),
                new SqlParameter("@Numbers", SqlDbType.VarChar,50)
            };
            parameters1[0].Value = model.ReturnTime;

            parameters1[1].Value = model.Comment;
            parameters1[2].Value = model.ReturnState;
            parameters1[3].Value = model.Numbers;

            CommandInfo comd1 = new CommandInfo();
            comd1.Parameters = parameters1;
            comd1.CommandText = strSql1.ToString();

            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update InstrumentAndConsumables set ");
            strSql2.Append("Quantity=@Quantity");
            strSql2.Append(" where Numbers=@Numbers ");
            SqlParameter[] parameters2 =
            {
                new SqlParameter("@Quantity", SqlDbType.Int,4),
                new SqlParameter("@Numbers", SqlDbType.VarChar,30)
            };
            parameters2[0].Value = 1;
            parameters2[1].Value = model.Numbers;

            CommandInfo comd2 = new CommandInfo();
            comd2.Parameters = parameters2;
            comd2.CommandText = strSql2.ToString();

            List<CommandInfo> listcomd = new List<CommandInfo>();
            listcomd.Add(comd1);
            listcomd.Add(comd2);

            try
            {
                DbHelperSQL.ExecuteSqlTran(listcomd);
            }
            catch
            {
                throw (new Exception("数据库执行错误!"));
            }
        }

        public static DataSet BorrowedRecipients()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Recipients.Numbers,Recipients.Name,InstrumentAndConsumables.Standards,InstrumentAndConsumables.UnitPrice,InstrumentAndConsumables.FactoryCode,Recipients.MRecipients,Recipients.EncodedTime,Recipients.Operator,Recipients.BorrowedState ");
            strSql.Append("FROM Recipients ");
            strSql.Append("INNER JOIN InstrumentAndConsumables ");
            strSql.Append("ON Recipients.Numbers=InstrumentAndConsumables.Numbers");

            return DbHelperSQL.Query(strSql.ToString());
        }

        public static IList<ExtendModule.Recipients> GetBorrowedRecipientsList()
        {
            IList<ExtendModule.Recipients> recipients = new List<ExtendModule.Recipients>();
            ExtendModule.Recipients recipient;

            DataSet recipientset = BorrowedRecipients();

            foreach (DataRow dr in recipientset.Tables[0].Rows)
            {
                recipient=new ExtendModule.Recipients();
                recipient.Numbers = dr["Numbers"].ToString();
                recipient.Name = dr["Name"].ToString();
                recipient.Standards = dr["Standards"].ToString();
                recipient.UnitPrice = dr["UnitPrice"].ToString();                
                recipient.FactoryCode = dr["FactoryCode"].ToString();                
                recipient.MRecipients = dr["MRecipients"].ToString();
                recipient.EncodedTime = (DateTime)dr["EncodedTime"];
                recipient.Operator = dr["Operator"].ToString(); 
                recipient.BorrowedState = dr["BorrowedState"].ToString();
                recipients.Add(recipient);
            }

            return recipients;
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<ExtendModule.Recipients> GetExtendModelList(string strWhere)
        {
            DataSet ds = dal.GetInnerList(strWhere);
            List<ExtendModule.Recipients> modelList = new
                List<ExtendModule.Recipients>();
            int rowsCount = ds.Tables[0].Rows.Count;
            if (rowsCount > 0)
            {
                ExtendModule.Recipients model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new ExtendModule.Recipients();
                    model.ID = long.Parse(ds.Tables[0].Rows[n]["ID"].ToString());
                    model.Numbers = ds.Tables[0].Rows[n]["Numbers"].ToString();
                    model.Type = ds.Tables[0].Rows[n]["Type"].ToString();
                    model.Name = ds.Tables[0].Rows[n]["Name"].ToString();
                    model.MRecipients = ds.Tables[0].Rows[n]["MRecipients"].ToString();
                    if (ds.Tables[0].Rows[n]["EncodedTime"].ToString() != "")
                    {
                        model.EncodedTime = DateTime.Parse(ds.Tables[0].Rows[n]["EncodedTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ReturnTime"].ToString() != "")
                    {
                        model.ReturnTime = DateTime.Parse(ds.Tables[0].Rows[n]["ReturnTime"].ToString());
                    }
                    model.BorrowedState = ds.Tables[0].Rows[n]["BorrowedState"].ToString();
                    model.Comment = ds.Tables[0].Rows[n]["Comment"].ToString();
                    model.Operator = ds.Tables[0].Rows[n]["Operator"].ToString();
                    if (ds.Tables[0].Rows[n]["OperatDate"].ToString() != "")
                    {
                        model.OperatDate = DateTime.Parse(ds.Tables[0].Rows[n]["OperatDate"].ToString());
                    }
                    model.Standards = ds.Tables[0].Rows[n]["Standards"].ToString();
                    model.UnitPrice = ds.Tables[0].Rows[n]["UnitPrice"].ToString();
                    model.FactoryCode = ds.Tables[0].Rows[n]["FactoryCode"].ToString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }

    }
}
